#include "dao.h"
#include <QtSql/qsqlrecord.h>
#include <qsqlerror.h>
#include <qsqlquery.h>
#include <qdebug.h>
#include <qstring.h>
#include <qdatetime.h>
#include <qmap.h>


Dao::Dao(QObject* parent) : QObject(parent) {}

bool Dao::initDatabase(Configuration::DataBaseConfig& config) {
    QString connectName = "INIT";
    QSqlDatabase database;
    //设置数据库连接
    try {
        if (QSqlDatabase::contains(connectName)) {
            database = QSqlDatabase::database(connectName);
        } else {
            database = QSqlDatabase::addDatabase("QMYSQL3", connectName);
        }
        //先关掉数据库连接
        if (database.isOpen()) {
            database.close();
        }
        // 连接数据库
        database.setHostName(config.host);
        database.setPort(config.port);
        database.setUserName(config.username);
        database.setPassword(config.password);
        //打开数据库连接
        if (database.open()) {
            //加载数据库配置文件
            QFile sqlFile("database.sql");
            if (sqlFile.exists()) {
                if (sqlFile.open(QIODevice::ReadOnly | QIODevice::Text)) {
                    QString text = QString::fromLocal8Bit(sqlFile.readAll());
                    sqlFile.close();
                    QString sql = QString("create database if not exists %1; use %2; %3").arg(config.database).arg(config.database).arg(text);
                    sql = sql.replace("\n", "");
                    sql = sql.replace("\\n", "");
                    //qDebug() << QString(sql.toLocal8Bit());
                    //执行SQL语句
                    database.exec(sql);
                    database.close();
                    qInfo() << "database init scussess...";
                    if (database.lastError().type() != QSqlError::NoError) {
                        //throw MyError(MyError::SQL_INIT_ERROR);
                        throw MyError(MyErrorType::DATABASE_INIT_ERROR);
                    }
                    return true;
                }
            }
        }
    } catch (const QSqlError& e) {
        qCritical() << database.lastError();
        //throw MyError(MyError::SQL_CONNECT_ERROR);
        throw MyError(MyErrorType::DATABASE_CONNECT_ERROR);
    } catch (const std::exception& e) {
        qCritical() << e.what();
        //throw MyError(MyError::SQL_CONNECT_ERROR);
        throw MyError(MyErrorType::DATABASE_CONNECT_ERROR);
    } catch (...) {
        //throw MyError(MyError::SQL_CONNECT_ERROR);
        throw MyError(MyErrorType::DATABASE_CONNECT_ERROR);
    }
    return false;
}

bool Dao::openDb(QString connectName, Configuration::DataBaseConfig& config) {
    this->connectName = connectName;
    try {
        //判断数据库是否已经连接
        if (!database.isOpen()) {
            //判断数据库是否已经连接，若未连接，则进行数据库连接
            if (QSqlDatabase::contains(connectName)) {
                database = QSqlDatabase::database(connectName);
            } else {
                database = QSqlDatabase::addDatabase("QMYSQL3", connectName);
            }
            // 设置数据库参数
            database.setHostName(config.host);
            database.setPort(config.port);
            database.setUserName(config.username);
            database.setPassword(config.password);
            database.setDatabaseName(config.database);
            //设置数据库超时断开后重新连接
            database.setConnectOptions("MYSQL_OPT_RECONNECT=1");
            if (!database.open()) {
                qCritical() << "database not connect...";
                throw MyError(MyErrorType::DATABASE_CONNECT_ERROR);
            }
        }
    } catch (const QSqlError& e) {
        qCritical() << database.lastError();
        //throw MyError(MyError::SQL_CONNECT_ERROR);
        throw MyError(MyErrorType::DATABASE_CONNECT_ERROR);
    } catch (const exception& e) {
        qCritical() << e.what();
        //throw MyError(MyError::SQL_CONNECT_ERROR);
        throw MyError(MyErrorType::DATABASE_CONNECT_ERROR);
    } catch (...) {
        qCritical() << "UNKNOW_ERROR";
        //throw MyError(MyError::SQL_CONNECT_ERROR);
        throw MyError(MyErrorType::DATABASE_CONNECT_ERROR);
    }
    return database.isOpen();
}

bool Dao::isOpened() {
    return this->database.isOpen();
}

void Dao::addTestParam(const QString& tableName, const QString& name) {
    try {
        if (openDb()) {
            QSqlQuery query(this->database);
            QString sql = tr("INSERT INTO `%1`(name, last_use_time) values('%2', '%3')")
                .arg(tableName)
                .arg(name.toStdString().c_str())
                .arg(QDateTime::currentDateTime().toString(TimeUtils::DATETIME_FORMAT));
            query.exec(sql);
            qInfo() << query.lastQuery();
            if (QSqlError::NoError != query.lastError().type()) {
                qCritical() << database.lastError();
                //throw MyError(MyError::SQL_INSERT_ERROR);
                throw MyError(MyErrorType::DATABASE_INSERT_ERROR);
            }
        }
    } catch (const MyError& e) {
        qCritical() << e.what();
        throw e;
    } catch (const QSqlError& e) {
        qCritical() << database.lastError();
        //throw MyError(MyError::SQL_INSERT_ERROR);
        throw MyError(MyErrorType::DATABASE_INSERT_ERROR);
    } catch (const exception& e) {
        qCritical() << e.what();
        //throw MyError(MyError::SQL_INSERT_ERROR);
        throw MyError(MyErrorType::DATABASE_INSERT_ERROR);
    } catch (...) {
        qCritical() << "UNKNOW_ERROR";
        //throw MyError(MyError::SQL_INSERT_ERROR);
        throw MyError(MyErrorType::DATABASE_INSERT_ERROR);
    }

}

void Dao::queryTestParam(const QString& tableName, QStringList& names) {
    try {
        if (openDb()) {
            QSqlQuery query(this->database);
            query.exec(tr("SELECT * FROM `%1` ORDER BY `last_use_time` DESC").arg(tableName));
            qInfo() << query.lastQuery();
            if (QSqlError::NoError == query.lastError().type()) {
                while (query.next()) {
                    names.push_back(query.value("name").toString());
                }
            } else {
                qCritical() << database.lastError();
                //throw MyError(MyError::SQL_QUERY_ERROR);
                throw MyError(MyErrorType::DATABASE_QUERY_ERROR);
            }
        }
    } catch (const MyError& e) {
        qCritical() << e.what();
        throw e;
    } catch (const QSqlError& e) {
        qCritical() << database.lastError();
       // throw MyError(MyError::SQL_QUERY_ERROR);
        throw MyError(MyErrorType::DATABASE_QUERY_ERROR);
    } catch (const exception& e) {
        qCritical() << e.what();
        //throw MyError(MyError::SQL_QUERY_ERROR);
        throw MyError(MyErrorType::DATABASE_QUERY_ERROR);
    } catch (...) {
        qCritical() << "UNKNOW_ERROR";
        //throw MyError(MyError::SQL_QUERY_ERROR);
        throw MyError(MyErrorType::DATABASE_QUERY_ERROR);
    }
}

void Dao::queryHistoryTestParams(QStringList& testers, QStringList& schedules) {
    try {
        if (openDb()) {
            QSqlQuery query(this->database);
            //查询检测员
            query.exec("SELECT distinct `tester` FROM `data`");
            qDebug() << query.lastQuery();
            if (QSqlError::NoError == query.lastError().type()) {
                while (query.next()) {
                    QString tester = query.value("tester").toString();
                    if (!tester.isEmpty()) {
                        testers.push_back(tester);
                    }
                }
            } else {
                qCritical() << database.lastError();
                //throw MyError(MyError::SQL_QUERY_ERROR);
                throw MyError(MyErrorType::DATABASE_QUERY_ERROR);
            }
            //查询班次
            query.exec("SELECT distinct `schedule` FROM `data`");
            qDebug() << query.lastQuery();
            if (QSqlError::NoError == query.lastError().type()) {
                while (query.next()) {
                    QString schedule = query.value("schedule").toString();
                    if (!schedule.isEmpty()) {
                        schedules.push_back(schedule);
                    }
                }
            } else {
                qCritical() << database.lastError();
               // throw MyError(MyError::SQL_QUERY_ERROR);
                throw MyError(MyErrorType::DATABASE_QUERY_ERROR);
            }
       }
    } catch (const MyError& e) {
        qCritical() << e.what();
        throw e;
    } catch (const QSqlError& e) {
        qCritical() << database.lastError();
       // throw MyError(MyError::SQL_QUERY_ERROR);
        throw MyError(MyErrorType::DATABASE_QUERY_ERROR);
    } catch (const exception& e) {
        qCritical() << e.what();
        //throw MyError(MyError::SQL_QUERY_ERROR);
        throw MyError(MyErrorType::DATABASE_QUERY_ERROR);
    } catch (...) {
        qCritical() << "UNKNOW_ERROR";
        //throw MyError(MyError::SQL_QUERY_ERROR);
        throw MyError(MyErrorType::DATABASE_QUERY_ERROR);
    }
}

void Dao::deleteTestParam(const QString& tableName, const QString& name) {
    try {
        if (openDb()) {
            QSqlQuery query(this->database);
            QString sql = tr("DELETE FROM `%1` where `name`='%2'").arg(tableName).arg(name);
            query.exec(sql);
            qInfo() << query.lastQuery();
            if (QSqlError::NoError != query.lastError().type()) {
                qCritical() << database.lastError();
                //throw MyError(MyError::SQL_DELETE_ERROR);
                throw MyError(MyErrorType::DATABASE_DELETE_ERROR);
            }
        }
    } catch (const MyError& e) {
        qCritical() << e.what();
        throw e;
    } catch (const QSqlError& e) {
        qCritical() << database.lastError();
        //throw MyError(MyError::SQL_DELETE_ERROR);
        throw MyError(MyErrorType::DATABASE_DELETE_ERROR);
    } catch (const exception& e) {
        qCritical() << e.what();
        //throw MyError(MyError::SQL_DELETE_ERROR);
        throw MyError(MyErrorType::DATABASE_DELETE_ERROR);
    } catch (...) {
        qCritical() << "UNKNOW_ERROR";
        //throw MyError(MyError::SQL_DELETE_ERROR);
        throw MyError(MyErrorType::DATABASE_DELETE_ERROR);
    }
}

void Dao::updateTestParamLastUseTime(const QString& tableName, const QString& name) {
    try {
        if (openDb()) {
            QSqlQuery query(this->database);
            QString sql = tr("UPDATE `%1` SET `last_use_time`='%2' where `name`='%3'")
                .arg(tableName).arg(QDateTime::currentDateTime().toString(TimeUtils::DATETIME_FORMAT)).arg(name);
            query.exec(sql);
            qInfo() << query.lastQuery();
            if (QSqlError::NoError != query.lastError().type()) {
                qCritical() << database.lastError();
                //throw MyError(MyError::SQL_UPDATE_ERROR);
                throw MyError(MyErrorType::DATABASE_UPDATE_ERROR);
            }
        }
    } catch (const MyError& e) {
        qCritical() << e.what();
        throw e;
    } catch (const QSqlError& e) {
        qCritical() << database.lastError();
        //throw MyError(MyError::SQL_UPDATE_ERROR);
        throw MyError(MyErrorType::DATABASE_UPDATE_ERROR);
    } catch (const exception& e) {
        qCritical() << e.what();
        //throw MyError(MyError::SQL_UPDATE_ERROR);
        throw MyError(MyErrorType::DATABASE_UPDATE_ERROR);
    } catch (...) {
        qCritical() << "UNKNOW_ERROR";
        //throw MyError(MyError::SQL_UPDATE_ERROR);
        throw MyError(MyErrorType::DATABASE_UPDATE_ERROR);
    }
}

void Dao::addTestData(DaoModel::TestData& data) {
    try {
        if (openDb()) {
            QSqlQuery query(this->database);
            //判断是否存在相同的记录，若不存在，则添加
            QStringList sql;
            sql << "INSERT INTO `data` (`id`, `tester`, `schedule`, `brand`, `num`, `maxHeight`, minHeight, "
                << "meanHeight, heightSD, abnormalArea, create_time) values ("
                << tr("%1,").arg(data.id())
                << tr("'%1',").arg(data.tester())
                << tr("'%1',").arg(data.schedule())
                << tr("'%1',").arg(data.brand())
                << tr("'%1',").arg(data.num())
                << tr("'%1',").arg(data.maxHeight())
                << tr("%1,").arg(data.minHeight())
                << tr("%1,").arg(data.meanHeight())
                << tr("'%1',").arg(data.heightSD())
                << tr("%1,").arg(data.abnormalArea())
                << tr("'%1')").arg(data.createTime().toString("yyyy-MM-dd HH:mm:ss"));
            query.exec(sql.join(' '));
            qInfo() << query.lastQuery();
            if (QSqlError::NoError != query.lastError().type()) {
                qCritical() << database.lastError();
               // throw MyError(MyError::SQL_INSERT_ERROR);
                throw MyError(MyErrorType::DATABASE_INSERT_ERROR);
            }
        }
    } catch (const MyError& e) {
        qCritical() << e.what();
        throw e;
    } catch (const QSqlError& e) {
        qCritical() << database.lastError();
        //throw MyError(MyError::SQL_INSERT_ERROR);
        throw MyError(MyErrorType::DATABASE_INSERT_ERROR);
    } catch (const exception& e) {
        qCritical() << e.what();
       // throw MyError(MyError::SQL_INSERT_ERROR);
        throw MyError(MyErrorType::DATABASE_INSERT_ERROR);
    } catch (...) {
        qCritical() << "UNKNOW_ERROR";
        //throw MyError(MyError::SQL_INSERT_ERROR);
        throw MyError(MyErrorType::DATABASE_INSERT_ERROR);
    }
}

void Dao::deleteRecord(qint64 id) {
    try {
        if (openDb()) {
            QSqlQuery query(this->database);
            query.exec(QString("DELETE FROM `data` where `id`=%1").arg(id));
            qInfo() << query.lastQuery();
            if (QSqlError::NoError != query.lastError().type()) {
                qCritical() << database.lastError();
                throw MyError(MyErrorType::DATABASE_DELETE_ERROR);
            }
        }
    } catch (const MyError& e) {
        qCritical() << e.what();
        throw e;
    } catch (const QSqlError& e) {
        qCritical() << database.lastError();
        throw MyError(MyErrorType::DATABASE_DELETE_ERROR);
    } catch (const exception& e) {
        qCritical() << e.what();
        throw MyError(MyErrorType::DATABASE_DELETE_ERROR);
    } catch (...) {
        qCritical() << "UNKNOW_ERROR";
        throw MyError(MyErrorType::DATABASE_DELETE_ERROR);
    }
}

void Dao::deleteRecords(vector<qint64>& recordIds) {
    try {
        if (openDb()) {
            QSqlQuery query(this->database);
            //将长整形数据转成字符串并作为参数传入SQL语句
            QStringList idList;
            for (int i = 0; i < recordIds.size(); i++) {
                idList << QString::number(recordIds[i]);
            }
            //删除检测记录
            query.exec(QString("DELETE FROM `data` WHERE `id` in (%1)").arg(idList.join(",")));
            qInfo() << query.lastQuery();
            if (QSqlError::NoError != query.lastError().type()) {
                qCritical() << database.lastError();
                throw MyError(MyErrorType::DATABASE_DELETE_ERROR);
            }
        }
    } catch (const MyError& e) {
        qCritical() << e.what();
        throw e;
    } catch (const QSqlError& e) {
        qCritical() << database.lastError();
        throw MyError(MyErrorType::DATABASE_DELETE_ERROR);
    } catch (const exception& e) {
        qCritical() << e.what();
        throw MyError(MyErrorType::DATABASE_DELETE_ERROR);
    } catch (...) {
        qCritical() << "UNKNOW_ERROR";
        throw MyError(MyErrorType::DATABASE_DELETE_ERROR);
    }
}

void Dao::deleteRecords(QDate& beginDay, QDate& endDay, QString tester, QString schedule) {
    try {
        if (openDb()) {
            QSqlQuery query(this->database);
            this->database.transaction();
            //开始时间
            QString beginTime = QString("%1 00:00:00").arg(beginDay.toString(TimeUtils::DAY_FORMAT));
            //结束时间
            QString endTime = QString("%1 00:00:00").arg(endDay.addDays(1).toString(TimeUtils::DAY_FORMAT));
            //创建SQL语句
            QStringList sql;
            sql << "delete FROM `data` where ";
            sql << QString("create_time >= '%1' and create_time < '%2'").arg(beginTime).arg(endTime);
            if (!tester.isEmpty()) {
                sql << QString(" and tester='%1'").arg(tester);
            }
            if (!schedule.isEmpty()) {
                sql << QString(" and schedule='%1'").arg(schedule);
            }
            //删除检测记录
            query.exec(sql.join(" "));
            this->database.commit();
            qInfo() << query.lastQuery();
            if (QSqlError::NoError != query.lastError().type()) {
                qCritical() << database.lastError();
                throw MyError(MyErrorType::DATABASE_DELETE_ERROR);
            }
        }
    } catch (const MyError& e) {
        qCritical() << e.what();
        this->database.rollback();
        throw e;
    } catch (const QSqlError& e) {
        qCritical() << database.lastError();
        this->database.rollback();
        throw MyError(MyErrorType::DATABASE_DELETE_ERROR);
    } catch (const exception& e) {
        qCritical() << e.what();
        this->database.rollback();
        throw MyError(MyErrorType::DATABASE_DELETE_ERROR);
    } catch (...) {
        qCritical() << "UNKNOW_ERROR";
        this->database.rollback();
        throw MyError(MyErrorType::DATABASE_DELETE_ERROR);
    }
}

void Dao::queryTestData(QList<DaoModel::TestData>& dataList, QString createTime) {
    try {
        openDb();
        QSqlQuery query(this->database);
        //判断是否存在相同的记录，若不存在，则添加
        QString sql = tr("SELECT * FROM `data` WHERE `create_time`='%1'").arg(createTime);
        query.exec(sql);
        qInfo() << query.lastQuery();
        if (QSqlError::NoError == query.lastError().type()) {
            while (query.next()) {
                DaoModel::TestData data;
                data.setId(query.value("id").toULongLong());
                data.setTester(query.value("tester").toString());
                data.setSchedule(query.value("schedule").toString());
                data.setBrand(query.value("brand").toString());
                data.setNum(query.value("num").toString());
                data.setMaxHeight(query.value("maxHeight").toDouble());
                data.setMinHeight(query.value("minHeight").toDouble());
                data.setMeanHeight(query.value("meanHeight").toDouble());
                data.setHeightSD(query.value("heightSD").toDouble());
                data.setAbnormalArea(query.value("abnormalArea").toDouble());
                dataList.push_back(data);
            }
        } else {
            qCritical() << database.lastError();
            throw MyError(MyErrorType::DATABASE_QUERY_ERROR);
        }
    } catch (const MyError& e) {
        qCritical() << e.what();
        throw e;
    } catch (const QSqlError& e) {
        qCritical() << database.lastError();
        throw MyError(MyErrorType::DATABASE_QUERY_ERROR);
    } catch (const exception& e) {
        qCritical() << e.what();
        throw MyError(MyErrorType::DATABASE_QUERY_ERROR);
    } catch (...) {
        qCritical() << "UNKNOW_ERROR";
        throw MyError(MyErrorType::DATABASE_QUERY_ERROR);
    }
}

void Dao::queryTestRecords(QList<DaoModel::TestData>& records, QDate& beginDay, QDate& endDay, QString tester, QString schedule) {
    try {
        if (openDb()) {
            //开始时间
            QString beginTime = QString("%1 00:00:00").arg(beginDay.toString(TimeUtils::DAY_FORMAT));
            //结束时间
            QString endTime = QString("%1 00:00:00").arg(endDay.addDays(1).toString(TimeUtils::DAY_FORMAT));
            //创建SQL语句
            QStringList sql;
            sql << "select DISTINCT create_time, tester, schedule FROM `data` where ";
            sql << QString("create_time >= '%1' and create_time < '%2'").arg(beginTime).arg(endTime);
            if (!tester.isEmpty()) {
                sql << QString(" and tester='%1'").arg(tester);
            }
            if (!schedule.isEmpty()) {
                sql << QString(" and schedule='%1'").arg(schedule);
            }
            sql << " ORDER BY create_time ASC";
            QSqlQuery query(this->database);
            query.exec(sql.join(" "));
            qInfo() << query.lastQuery();
            //封装数据
            if (QSqlError::NoError == query.lastError().type()) {
                while (query.next()) {
                    DaoModel::TestData record;
                    record.setCreateTime(query.value("create_time").toDateTime());
                    record.setTester(query.value("tester").toString());
                    record.setSchedule(query.value("schedule").toString());
                    records.push_back(record);
                }
            } else {
                qCritical() << database.lastError();
                throw MyError(MyErrorType::DATABASE_QUERY_ERROR);
            }
        }
    } catch (const MyError& e) {
        qCritical() << e.what();
    } catch (const QSqlError& e) {
        qCritical() << database.lastError();
        throw MyError(MyErrorType::DATABASE_QUERY_ERROR);
    } catch (const exception& e) {
        qCritical() << e.what();
        throw MyError(MyErrorType::DATABASE_QUERY_ERROR);
    } catch (...) {
        qCritical() << "UNKNOW_ERROR";
        throw MyError(MyErrorType::DATABASE_QUERY_ERROR);
    }
}